# -*- coding: utf-8 -*-
# @desc:
# @file: oracle_helper.py
# @author: Lin Jackey Lyu (me@linlyu.com)
# @date: 2018/7/27 19:10
import cx_Oracle
import logbook


class OracleHelper(object):
    """"""

    def __init__(self, conf):
        """Constructor for OracleHelper"""
        self.logger = logbook.Logger(self.__class__.__name__)
        self.logger.info("Oracle db configuration {}", conf)
        self.logger.info("Connecting to given db...")
        dsn_tns = cx_Oracle.makedsn(conf["host"], conf["port"], conf["sid"])
        self.db = cx_Oracle.connect(conf["user"], conf["password"], dsn_tns)
        self.sql = None
        self.cursor = self.db.cursor()
        self.logger.info("DB is connected.")

    def truncate_table(self, table):
        self.logger.info("Truncating table {}", table)
        self.cursor.execute("truncate table {}".format(table))
        self.logger.info("Table {} is truncated.", table)

    def prepare_sql(self, table, keys, macros):
        self.sql = "INSERT INTO {} ".format(table)
        self.sql += " ({}) ".format(",".join(list(keys) + list(macros.keys())))
        self.sql += " VALUES ({}".format(",".join(":" + str(i) for i in range(1, len(keys) + 1)))
        self.sql += ", {})".format(",".join(macros.values()))

        self.cursor.prepare(self.sql)
        self.logger.info("insert sql is {}", self.sql)

    def batch_insert(self, data):
        self.cursor.executemany(None, data)
        self.db.commit()


    def close(self):
        if self.db is not None:
            self.cursor.close()
            self.db.close()
        self.logger.info("Database is closed.")

